package com.os;

import com.aspose.cells.Workbook;
import com.aspose.cells.WorkbookDesigner;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author Matt
 * @Date 2022/10/13
 * @Desc: 生成excel格式的风险分析报告
 * @Version V1.0
 */
public class SelectRptExcel {

    public static void main(String[] args) {

        //读取授权文件
        if(!CommUtil.getLicense(CommUtil.Excel))
        {
            System.out.println("excel license load error!");
        }

        String sFilePath = "C:\\EXCEL.xls";
        try {
            // 读取模板文件，FilePath为文件的路径
            InputStream in = new FileInputStream(new File(sFilePath));
            //读取工作簿
            Workbook wb= new Workbook(in);
            //记得关闭输入流
            in.close();
            //创建Aspose.Cells WorkbookDesigner 的对象
            WorkbookDesigner designer = new WorkbookDesigner();
            designer.setWorkbook(wb);
            //将查询结果存放到list中
            List<Map<String, Object>> dataList = new ArrayList<>();
            Map<String, Object> map = new HashMap<>();
            map.put("NAME", "AAA");
            map.put("SEX", "BBB");
            map.put("ID", "CCC");
            map.put("PHONE", "DDD");
            map.put("ADDRESS", "EEE");
            dataList.add(map);
            Map<String, Object> map1 = new HashMap<>();
            map1.put("NAME", "宋传磊");
            map1.put("SEX", "男");
            map1.put("ID", "320721");
            map1.put("PHONE", "18251303287");
            map1.put("ADDRESS", "江苏无锡");
            dataList.add(map1);
            List<Map<String, Object>> dataList1 = new ArrayList<>();
            dataList1.add(map);
            Map<String, Object> user = new HashMap<>();
            user.put("用户", "小宋");
            user.put("部门", "研发部");
            user.put("状态", "正在工作");
            user.put("健康", "健康");

            List<Map<String, Object>> addTaxDataList = new ArrayList<>();
            Map<String, Object> tax = new HashMap<>();
            tax.put("年度", 2021);
            tax.put("按适用税率计税销售额", 29329.01);
            tax.put("按简易办法计税销售额", 3788.99);
            tax.put("免抵退出口销售额", 32787);
            tax.put("销项税额", 322);
            tax.put("进项税额", 9886);
            addTaxDataList.add(tax);
            tax = new HashMap<>();
            tax.put("年度", 2022);
            tax.put("按适用税率计税销售额", 29329.01);
            tax.put("按简易办法计税销售额", 3788.99);
            tax.put("免抵退出口销售额", 32787);
            tax.put("销项税额", 322);
            tax.put("进项税额", 9886);
            addTaxDataList.add(tax);

            //设置DataTable对象
            designer.setDataSource("user", new HashMapDataTable(user));
            designer.setDataSource("var", new HashMapDataTable(dataList));
            designer.setDataSource("var1", new HashMapDataTable(dataList1));
            designer.setDataSource("ADDTAX", new HashMapDataTable(addTaxDataList));
            //根据数据源处理生成报表内容
            designer.process();
            //输出流，写入到文件中
            OutputStream out = new FileOutputStream("C:\\导出数据.xls");
            //将查询数据写入到文件中
            wb.save(out, wb.getFileFormat());
            wb.dispose();
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }




    }
}
